package ist.utl.pt.smartfleet.stationterminal;


import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;



public class BDAdapter {
	
	int _idStation;
	int idL = 0;
	
	private static final String LOCATION_TABLE = "location";
    private static final String KEY_ID = "_id";
    private static final String KEY_ADDRESS = "name";
    private static final String KEY_LAT = "latitude";
    private static final String KEY_LON = "longitude";
    
    private static final String STATION_TABLE = "station";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_STATION_STATE = "state";

    private static final String VEHICLE_TABLE = "vehicle";
    private static final String KEY_BATT = "battery";
    private static final String KEY_VEHICLE_STATE = "state";
    private static final String KEY_VEHICLE_ADDR = "address";
    private static final String KEY_VEHICLE_PORT = "port";
    
    private static final String TRAVELLER_TABLE = "traveller";
    private static final String KEY_NAME = "name";
    private static final String KEY_TRAVELLER_STATE = "state";
    private static final String KEY_SIZE = "size";
    private static final String KEY_LOCATION_ADR = "location_adr";
    private static final String KEY_CHECKIN = "checkin";
    private static final String KEY_STATIONID = "station_id";
    
    private static final String TRIP_TABLE = "trip";
    private static final String KEY_VEHICLEID = "vehicle_id";
    private static final String KEY_TRAVELLERID = "traveller_id";

    private static final String TAG = "BDAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    /**
     * Database creation sql statement
     */
    private static final String CREATE_LOCATION = 
    	"create table "+LOCATION_TABLE+" ("+KEY_ID+" text primary key, "
        +KEY_ADDRESS+" text not null, "+KEY_LAT+" real not null, "+KEY_LON+" real not null)";
    
    /*
     * id location state  KEY_STATION_NAME
     * 
     * */ 
    private static final String CREATE_STATION = 
    	"create table "+STATION_TABLE+" ("
    	+KEY_ID+" integer primary key, " 
    	+KEY_LOCATION+" integer not null, "
    	+KEY_STATION_STATE+" text not null, " 
    	+"FOREIGN KEY("+KEY_LOCATION+") "+"REFERENCES "+LOCATION_TABLE+"("+KEY_ID+"))";
    
    /*
     * id bateria estado
     * */
    private static final String CREATE_VEHICLE = 
    	"create table "+VEHICLE_TABLE+" ("
    	+KEY_ID+" integer primary key, "
    	+KEY_BATT+" integer not null, "
    	+KEY_VEHICLE_STATE+" text not null, "
    	+KEY_VEHICLE_ADDR+" text not null, "
    	+KEY_VEHICLE_PORT+" integer not null)";
    
    /*
     * id estado size location checkin id_estação
     * */
    private static final String CREATE_TRAVELLER = 
    	"create table "+TRAVELLER_TABLE+" ("
    	+KEY_ID+" text primary key, "
    	+KEY_NAME+" text not null, "
    	+KEY_TRAVELLER_STATE+" text not null, "
    	+KEY_SIZE+" integer not null, "
    	+KEY_LOCATION_ADR+" integer not null, "
    	+KEY_CHECKIN+" integer not null, "
    	+KEY_STATIONID+" integer not null, "
    	+"FOREIGN KEY("+KEY_LOCATION_ADR+") REFERENCES "+LOCATION_TABLE+"("+KEY_ID+"))";
    
    private static final String CREATE_TRIP = 
    	"create table "+TRIP_TABLE+" ("+KEY_VEHICLEID+" integer, "+KEY_TRAVELLERID+" integer, "
    	+"primary key("+KEY_VEHICLEID+", "+KEY_TRAVELLERID+"), "
    	+"FOREIGN KEY("+KEY_VEHICLEID+") REFERENCES "+VEHICLE_TABLE+"("+KEY_ID+"), "
    	+"FOREIGN KEY("+KEY_TRAVELLERID+") REFERENCES "+TRAVELLER_TABLE+"("+KEY_ID+"))";
    

    private static final String DATABASE_NAME = "stationBD";
    private static final int DATABASE_VERSION = 2;
    
    private final Context mCtx;
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        	// Criar tabelas
            db.execSQL(CREATE_LOCATION);
            db.execSQL(CREATE_STATION);
            db.execSQL(CREATE_VEHICLE);
            db.execSQL(CREATE_TRAVELLER);
            db.execSQL(CREATE_TRIP);
            
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public BDAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public BDAdapter open(int id) throws SQLException {
    	_idStation = id;
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

public void close() {
    	
    	// TODO limpar tabelas
    	mDb.execSQL("delete from "+STATION_TABLE);
    	mDb.execSQL("delete from "+VEHICLE_TABLE);
    	mDb.execSQL("delete from "+LOCATION_TABLE);
    	mDb.execSQL("delete from "+TRAVELLER_TABLE);
    	mDb.execSQL("delete from "+TRIP_TABLE);
        mDbHelper.close();
    }

public void pause() {
    mDbHelper.close();
}

    public long initiateStation(int id, String state, String id_location){
    	        	
    	ContentValues stationValues = new ContentValues();
    		
    	stationValues.put(KEY_ID, id);
    	stationValues.put(KEY_STATION_STATE, state);
    	stationValues.put(KEY_LOCATION, id_location);
        
        return mDb.insert(STATION_TABLE, null, stationValues);
    } 
    
    /*
     * FUNÇOES PARA STATION
     * 
     * */
    public void setStationState(String state){
    	
    	ContentValues Values = new ContentValues();
        Values.put(KEY_STATION_STATE, state);
    	
    	mDb.update(VEHICLE_TABLE, Values, KEY_ID +" = "+ _idStation, null);
    	
    }
    
    public String getStationLocation(int id){
    	
    	Cursor mCursor = mDb.query(true, STATION_TABLE, new String[] {KEY_LOCATION}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	String ida = mCursor.getString(0);
    	
    	mCursor.close();
    	
    	return ida;
	
    }
    
    public long createStation(int id, double lat, double lon, String morada){
    	
    	String idS = createLocation(morada, lat, lon);
    	
    	ContentValues values = new ContentValues();
        values.put(KEY_ID, id);
        values.put(KEY_LOCATION, idS);
        values.put(KEY_STATION_STATE, "on");
      
        return mDb.insert(STATION_TABLE, null, values);
	
    }
    
    public ArrayList<Integer> getStations(){
    	
    	ArrayList<Integer> idStations = new ArrayList<Integer>();
    
    	Cursor mCursor = mDb.query(true, STATION_TABLE, new String[] {KEY_ID}, null, null,
				null, null, null, null);
    	
    	if (mCursor.getCount() != 0){
    	mCursor.moveToFirst();
    	do{
    			idStations.add(mCursor.getInt(0));
    		
    	}while(mCursor.moveToNext());
    	
    	}
    	
    	mCursor.close();
    	
    	return idStations;
    }
    
    /*
     * FUNCOES PARA TRAVELLER
     * 
     * */
    public long createTraveller(String id, String name, int nr_passengres, String id_location, long time_checkin, int id_Station) {
    	
    	ContentValues values = new ContentValues();
        values.put(KEY_ID, id);
        values.put(KEY_NAME, name);
        values.put(KEY_TRAVELLER_STATE, "atStation");
        values.put(KEY_SIZE, nr_passengres);
        values.put(KEY_LOCATION_ADR, id_location);
        values.put(KEY_CHECKIN, time_checkin);
        values.put(KEY_STATIONID, id_Station);
        return mDb.insert(TRAVELLER_TABLE, null, values);
    }
    
    public ArrayList<String> getTravellerGroup(){
    	int size = 0;
    	ArrayList<String> idPassengers = new ArrayList<String>();
    	
    	Cursor mCursor2;
    	Cursor mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_ID, KEY_TRAVELLER_STATE, KEY_SIZE}, null, null,
				null, null, null, null);
    	
    	if (mCursor.getCount() != 0){
    	mCursor.moveToFirst();
    	do{
    		
    		if (mCursor.getString(1).compareTo("atStation") == 0){
    			idPassengers.add(mCursor.getString(0));
    			size += mCursor.getInt(2);
    			
    			mCursor2 = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_ID, KEY_TRAVELLER_STATE, KEY_SIZE}, null, null,
    					null, null, null, null);
    			mCursor2.moveToFirst();
        			
    			do{
    				if (mCursor.getInt(0) != mCursor2.getInt(0) && mCursor2.getString(1).compareTo("atStation") == 0
        			     					&& size+mCursor2.getInt(2) <= 4){
    					
    					idPassengers.add(mCursor2.getString(0));
    					size += mCursor2.getInt(2);
        				
    				}
    			}while(mCursor2.moveToNext());
    			
    			mCursor2.close();
    			break;
    		}
    		
    	}while(mCursor.moveToNext());
    	
    	}
    	
    	mCursor.close();
    	
    	return idPassengers;
    }
    
    public void setTravellerState(String id, String state){
    	
    	ContentValues Values = new ContentValues();
        Values.put(KEY_TRAVELLER_STATE, state);
    	
    	mDb.update(TRAVELLER_TABLE, Values, KEY_ID +" = "+ id, null);
    	
    }
    
    public String getTravellerLocation(String id){
    	
    	Cursor mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_LOCATION_ADR}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	String ida = mCursor.getString(0);
    	
    	mCursor.close();
    	
    	return ida;
	
    }
    
    public String getTravellerName(String id){
    	
    	Cursor mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_NAME}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	String name = mCursor.getString(0);
    	
    	mCursor.close();
    	
    	return name;
	
    }
    
    public int getTravellerSize(String id){
    	
    	Cursor mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_SIZE}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	int size = mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	return size;
	
    }
    
    public Long getTravellerTime(String id){
    	
    	Cursor mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_CHECKIN}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	Long time = mCursor.getLong(0);
    	
    	mCursor.close();
    	
    	return time;
	
    }
    
    /*
     * FUNCOES PARA VEHICLES
     * 
     * */
    public long createVehicle(int id, int bateria, String state, String address, int port) {
    	
    	ContentValues values = new ContentValues();
        values.put(KEY_ID, id);
        values.put(KEY_BATT, bateria);
        values.put(KEY_VEHICLE_STATE, state);
        values.put(KEY_VEHICLE_ADDR, address);
        values.put(KEY_VEHICLE_PORT, port);
        
        return mDb.insert(VEHICLE_TABLE, null, values);
    }
    
    public void setVheicleState(int id, String state){
	
    	ContentValues Values = new ContentValues();
    	Values.put(KEY_VEHICLE_STATE, state);
	
    	mDb.update(VEHICLE_TABLE, Values, KEY_ID +" = "+ id, null);
	
    }
    
    public void setVheicleBattery(int id, int bateria){
    	
    	ContentValues Values = new ContentValues();
        Values.put(KEY_BATT, bateria);
    	
    	mDb.update(VEHICLE_TABLE, Values, KEY_ID +" = "+ id, null);
    	
    }
    
    public String getVehicleAddr(int id){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_VEHICLE_ADDR}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	String adr = mCursor.getString(0);
    	
    	mCursor.close();
    	
    	return adr;
	
    }
    
    public String getVehicleState(int id){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_VEHICLE_STATE}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	String state = mCursor.getString(0);
    	
    	mCursor.close();
    	
    	return state;
	
    }
    
    public int getVehiclePort(int id){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_VEHICLE_PORT}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	int port = mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	return port;
    }
    

	public ArrayList<Integer> getAvailableVehicle(){
    	
    	ArrayList<Integer> idVehicles = new ArrayList<Integer>();
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ID, KEY_VEHICLE_STATE}, null, null,
				null, null, null, null);
    	

    	if (mCursor.getCount() == 0){
    		mCursor.close();
    		return idVehicles;}
    	mCursor.moveToFirst();
    	do{ 
    		if(mCursor.getString(1).compareTo("atStation") == 0)
    			idVehicles.add(mCursor.getInt(0));
    		
    	}while(mCursor.moveToNext());
    	
    	mCursor.close();
    	
    	return idVehicles;
    }
	
	public ArrayList<Integer> getVehicles(){
    	
    	ArrayList<Integer> idVehicles = new ArrayList<Integer>();
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ID, KEY_VEHICLE_STATE}, null, null,
				null, null, null, null);
    	

    	if (mCursor.getCount() == 0){
    		mCursor.close();
    		return idVehicles;}
    	
    	mCursor.moveToFirst();
    	do{ 
    			idVehicles.add(mCursor.getInt(0));
    		
    	}while(mCursor.moveToNext());
    	
    	mCursor.close();
    	
    	return idVehicles;
    }
    
    public int getVehicleBattery(int id){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_BATT}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	int bateria = mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	return bateria;
    }
    
    /*
     * FUCNOES PARA LOCATIONS 
     * 
     * */  //mudar o id das localizações
    public String createLocation(String address, double lat, double lon) {
    	
    	idL++;
    	//gerar id das localizações
    	String ide = _idStation+""+idL;
    	ContentValues values = new ContentValues();
        values.put(KEY_ID, ide);
        values.put(KEY_ADDRESS, address);
        values.put(KEY_LAT, lat);
        values.put(KEY_LON, lon);
        
        mDb.insert(LOCATION_TABLE, null, values);
        return ide;
    }

    public Location getLocationAdress(String id){
    	
    	Cursor mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_LAT, KEY_LON}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	double lat = mCursor.getDouble(0);
    	double lon = mCursor.getDouble(1);
    	
    	Location x = new Location("morada");
    	x.setLatitude(lat);
    	x.setLongitude(lon);
    	
    	mCursor.close();
    	
    	return x;
    }
    
    public String getLocationName(String id){
    	
    	Cursor mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_ADDRESS}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	String name = mCursor.getString(0);
    	
    	mCursor.close();
    	
    	return name;
    }
    
    
  //  TODO FAZER TRANSBORDO
    
    public void printDBTravellers(){
    	
    	Cursor mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_ID, KEY_NAME, KEY_TRAVELLER_STATE, KEY_SIZE,
    			KEY_LOCATION_ADR, KEY_CHECKIN, KEY_STATIONID}, null, null,
				null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	if(mCursor.getCount() == 0){
    		mCursor.close();
    		return;
    	}
    	
    	do{
    		System.out.println("------TRAVELLER TABLE--------------");
    		System.out.println("KEY_ID "+mCursor.getString(0));
    		System.out.println("KEY_NAME: "+mCursor.getString(1));
    		System.out.println("KEY_TRAVELLER_STATE: "+mCursor.getString(2));
    		System.out.println("KEY_SIZE: "+mCursor.getInt(3));
    		System.out.println("KEY_LOCATION_ADR: "+mCursor.getInt(4));
    		System.out.println("KEY_CHECKIN: "+mCursor.getLong(5));
    		System.out.println("KEY_STATIONID: "+mCursor.getInt(6));
    		

    	}while(mCursor.moveToNext());
    	
    	mCursor.close();
    }
    
    public void printDBVehicles(){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ID, KEY_BATT, KEY_VEHICLE_STATE, KEY_VEHICLE_ADDR,
    			KEY_VEHICLE_PORT}, null, null,
				null, null, null, null);
    	
    	
    	mCursor.moveToFirst();
    	if(mCursor.getCount() == 0){
    		mCursor.close();
    		return;
    	}
    	
    	do{
    		System.out.println("------VEHICLE TABLE--------------");
    		System.out.println("KEY_ID "+mCursor.getInt(0));
    		System.out.println("KEY_BATT: "+mCursor.getInt(1));
    		System.out.println("KEY_VEHICLE_STATE: "+mCursor.getString(2));
    		System.out.println("KEY_VEHICLE_ADDR: "+mCursor.getString(3));
    		System.out.println("KEY_VEHICLE_PORT: "+mCursor.getInt(4));
    		

    	}while(mCursor.moveToNext());
    	
    	mCursor.close();
    }
    
    public void printDBLocations(){
    	
    	Cursor mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_ID, KEY_ADDRESS, KEY_LAT, KEY_LON}, null, null,
				null, null, null, null);
    	
 	   	mCursor.moveToFirst();
 	   	do{
    		System.out.println("------Location TABLE--------------");
    		System.out.println("KEY_ID "+mCursor.getString(0));
    		System.out.println("KEY_ADDRESS: "+mCursor.getString(1));
    		System.out.println("KEY_LAT: "+mCursor.getDouble(2));
    		System.out.println("KEY_LON: "+mCursor.getDouble(3));
    		

    	}while(mCursor.moveToNext());
    	
    	mCursor.close();
  	  }

    public void printDBStation(){
	
		Cursor mCursor = mDb.query(true, STATION_TABLE, new String[] {KEY_ID, KEY_LOCATION, KEY_STATION_STATE}, null, null,
				null, null, null, null);
	
		mCursor.moveToFirst();
		do{
			System.out.println("------Station TABLE--------------");
			System.out.println("KEY_ID "+mCursor.getInt(0));
			System.out.println("KEY_LOCATION: "+mCursor.getInt(1));
			System.out.println("KEY_STATION_STATE: "+mCursor.getString(2));
		

		}while(mCursor.moveToNext());
	
		mCursor.close();
    }

}
